package iva;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.eclipse.core.databinding.observable.Realm;
import org.eclipse.jface.databinding.swt.SWTObservables;
import org.eclipse.swt.SWT;
import org.eclipse.swt.events.FocusAdapter;
import org.eclipse.swt.events.FocusEvent;
import org.eclipse.swt.events.SelectionAdapter;
import org.eclipse.swt.events.SelectionEvent;
import org.eclipse.swt.events.VerifyEvent;
import org.eclipse.swt.events.VerifyListener;
import org.eclipse.swt.graphics.Point;
import org.eclipse.swt.graphics.Rectangle;
import org.eclipse.swt.layout.FillLayout;
import org.eclipse.swt.layout.FormAttachment;
import org.eclipse.swt.layout.FormData;
import org.eclipse.swt.layout.FormLayout;
import org.eclipse.swt.widgets.Button;
import org.eclipse.swt.widgets.Combo;
import org.eclipse.swt.widgets.Composite;
import org.eclipse.swt.widgets.Control;
import org.eclipse.swt.widgets.Display;
import org.eclipse.swt.widgets.Label;
import org.eclipse.swt.widgets.Menu;
import org.eclipse.swt.widgets.MenuItem;
import org.eclipse.swt.widgets.Shell;
import org.eclipse.swt.widgets.Table;
import org.eclipse.swt.widgets.TableColumn;
import org.eclipse.swt.widgets.TableItem;
import org.eclipse.swt.widgets.Text;

import sueldos.Utility;

import com.swtdesigner.SWTResourceManager;


public class IvaGUI {
	protected Shell shlIvaListado;
	private Text txtIngreseElNombre;
	private Composite composite;
	private Composite composite_2;
	private Composite composite_1;
	private Table table;
	private static String tipo = "";
	private Text text;

	
	/**
	 * Launch the application.
	 * @param args
	 */
	
	public void buscarDatos(String base, String tipoBase, String busqueda, String fecha){
		try {
			Connection conexion = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+base, "", ""); 
			String query="SELECT distinct nombre, DATE_FORMAT(fecha, '%d-%m-%Y') AS fecha, " +
					"CONCAT(sucursal, '-' ,numero) AS factura, " +
					"letra, neto, iva, total, retencion, certificad, tipocomp FROM "+tipoBase+" " +
							"WHERE (nombre LIKE '%"+busqueda+"%') AND (fecha >= '"+fecha+"') " +
									"ORDER BY nombre, DATE(fecha) DESC";
			PreparedStatement ps = conexion.prepareStatement(query);
			ps.execute();
			
			ResultSet rs = ps.getResultSet();
			while (rs.next()){
				TableItem item = new TableItem(table, SWT.NONE);
					if (tipoBase.equals("ventas") && rs.getString(10).equals("8")){
						item.setText(0, rs.getString(1));
						item.setText(1, rs.getString(2));
						item.setText(2, rs.getString(9));
						item.setText(3, "R");
						item.setText(4, "");
						item.setText(5, "");
						item.setText(6, rs.getBigDecimal(8).setScale(2, BigDecimal.ROUND_HALF_EVEN).toString());
					}else{
						item.setText(0, rs.getString(1));
						item.setText(1, rs.getString(2));
						if (rs.getString(3) == null){
							item.setText(2, "");
						}else {
							item.setText(2, rs.getString(3));
						}
						item.setText(3, rs.getString(4));
						try {
							item.setText(4, rs.getBigDecimal(5).setScale(2, BigDecimal.ROUND_HALF_EVEN).toString());
							item.setText(5, rs.getBigDecimal(6).setScale(2, BigDecimal.ROUND_HALF_EVEN).toString());
							item.setText(6, rs.getBigDecimal(7).setScale(2, BigDecimal.ROUND_HALF_EVEN).toString());
						} catch (Exception e) {
						}
					}
			}
			ps.close();
			conexion.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void buscarFactura(String base, String tipoBase, String busqueda, String sucursa, String number){
		try {
			Connection conexion = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+base, "", ""); 
			
			//String query="SELECT distinct nombre, DATE_FORMAT(fecha, '%d-%m-%Y') AS fecha, CONCAT(sucursal, '-' ,numero) AS factura, letra, neto, iva, total, retencion, certificad, tipocomp FROM "+tipoBase+" WHERE (nombre LIKE '%"+busqueda+"%') AND (SUCURSAL = "+sucursa+" AND numero = "+numero+") ORDER BY nombre asc";
			
			String query="SELECT distinct nombre, DATE_FORMAT(fecha, '%d-%m-%Y') AS fecha, CONCAT(sucursal, '-' ,numero) AS factura, letra, neto, iva, total, retencion, certificad, tipocomp FROM "+tipoBase+" WHERE ((nombre LIKE ?) AND (SUCURSAL = ? AND numero = ?)) ORDER BY YEAR(fecha) desc, nombre asc";
			PreparedStatement ps = conexion.prepareStatement(query);
			ps.setString(1, "%"+busqueda+"%");
			ps.setString(2, sucursa);
			ps.setString(3, number);
			ps.execute();
			
			ResultSet rs = ps.getResultSet();
			while (rs.next()){
				TableItem item = new TableItem(table, SWT.NONE);
					if (tipoBase.equals("ventas") && rs.getString(10).equals("8")){
						item.setText(0, rs.getString(1));
						item.setText(1, rs.getString(2));
						item.setText(2, rs.getString(9));
						item.setText(3, "R");
						item.setText(4, "");
						item.setText(5, "");
						item.setText(6, rs.getBigDecimal(8).setScale(2, BigDecimal.ROUND_HALF_EVEN).toString());
					}else{
						item.setText(0, rs.getString(1));
						item.setText(1, rs.getString(2));
						if (rs.getString(3) == null){
							item.setText(2, "");
						}else {
							item.setText(2, rs.getString(3));
						}
						item.setText(3, rs.getString(4));
						try {
							item.setText(4, rs.getBigDecimal(5).setScale(2, BigDecimal.ROUND_HALF_EVEN).toString());
							item.setText(5, rs.getBigDecimal(6).setScale(2, BigDecimal.ROUND_HALF_EVEN).toString());
							item.setText(6, rs.getBigDecimal(7).setScale(2, BigDecimal.ROUND_HALF_EVEN).toString());
						} catch (Exception e) {
							
						}
					}
					
			}
			
			

			ps.close();
			conexion.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	
	
	public static void main(String[] args) {
		Display display = Display.getDefault();
		Realm.runWithDefault(SWTObservables.getRealm(display), new Runnable() {
			public void run() {
				try {
					IvaGUI window = new IvaGUI();
					window.open();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Open the window.
	 */
	public void open() {
		Display display = Display.getDefault();
		createContents();
		shlIvaListado.open();
		shlIvaListado.layout();
		while (!shlIvaListado.isDisposed()) {
			if (!display.readAndDispatch()) {
				display.sleep();
			}
		}
	}

	/**
	 * Create contents of the window.
	 */
	
	
	
	
	
	protected void createContents() {
		shlIvaListado = new Shell(SWT.MAX | SWT.MIN | SWT.CLOSE);

		/*
		shlIvaListado.addShellListener(new ShellAdapter() {
			public void shellClosed(ShellEvent e) {
				if (BuscarFactura.shlBuscarFactura.isDisposed()){
					
				}else{
					BuscarFactura.shlBuscarFactura.close();

				}
			}
		});*/
		
		shlIvaListado.setSize(857, 476);
		//shell.setSize(754, 538);
		shlIvaListado.setImage(SWTResourceManager.getImage(IvaGUI.class, "/imagenes/AppIcon.png"));
		shlIvaListado.setText("IVA - Listado por Proveedor");
		shlIvaListado.setLayout(new FormLayout());
		

		Rectangle bds = Display.getCurrent().getClientArea();
        Point p = shlIvaListado.getSize();

        int nLeft = (bds.width - p.x) / 2;
        int nTop = (bds.height - p.y) / 2;

        shlIvaListado.setBounds(nLeft, nTop, p.x, p.y);
		
		
		
		composite = new Composite(shlIvaListado, SWT.BORDER);
		composite.setLayout(new FormLayout());
		FormData fd_composite = new FormData();
		fd_composite.left = new FormAttachment(0, 10);
		fd_composite.right = new FormAttachment(100, -10);
		composite.setLayoutData(fd_composite);
		
		composite_2 = new Composite(shlIvaListado, SWT.BORDER);
		composite_2.setFont(SWTResourceManager.getFont("Calibri", 8, SWT.NORMAL));
		fd_composite.top = new FormAttachment(0, 10);
		composite_2.setLayout(new FillLayout(SWT.HORIZONTAL));
		FormData fd_composite_2 = new FormData();
		fd_composite_2.right = new FormAttachment(composite, 0, SWT.RIGHT);
		fd_composite_2.top = new FormAttachment(composite, 6);
		fd_composite_2.left = new FormAttachment(0, 10);
		fd_composite_2.bottom = new FormAttachment(100, -68);
		composite_2.setLayoutData(fd_composite_2);
		
		composite_1 = new Composite(shlIvaListado, SWT.NONE);
		composite_1.setLayout(new FormLayout());
		FormData fd_composite_1 = new FormData();
		fd_composite_1.bottom = new FormAttachment(100, -10);
		fd_composite_1.top = new FormAttachment(composite_2, 6);
		
		table = new Table(composite_2, SWT.BORDER |SWT.SINGLE | SWT.FULL_SELECTION );
		table.setFont(SWTResourceManager.getFont("Calibri", 11, SWT.NORMAL));
		table.setHeaderVisible(true);
		table.setLinesVisible(true);
		

		
		TableColumn tblclmnProveedor = new TableColumn(table, SWT.NONE);
		tblclmnProveedor.setWidth(250);
		tblclmnProveedor.setText("Proveedor");
		
		TableColumn tblclmnFecha = new TableColumn(table, SWT.NONE);
		tblclmnFecha.setWidth(80);
		tblclmnFecha.setText("Fecha");
		
		TableColumn tblclmnFactura = new TableColumn(table, SWT.NONE);
		tblclmnFactura.setWidth(100);
		tblclmnFactura.setText("Factura");
		
		TableColumn tblclmnTipo = new TableColumn(table, SWT.NONE);
		tblclmnTipo.setWidth(44);
		tblclmnTipo.setText("Tipo");
		
		TableColumn tblclmnSubtotal = new TableColumn(table, SWT.RIGHT);
		tblclmnSubtotal.setWidth(85);
		tblclmnSubtotal.setText("Subtotal");
		
		TableColumn tblclmnIva = new TableColumn(table, SWT.RIGHT);
		tblclmnIva.setWidth(77);
		tblclmnIva.setText("IVA");
		
		TableColumn tblclmnTotal = new TableColumn(table, SWT.RIGHT);
		tblclmnTotal.setWidth(84);
		tblclmnTotal.setText("Total");
		
		

		
		fd_composite_1.right = new FormAttachment(composite, 0, SWT.RIGHT);
		fd_composite_1.left = new FormAttachment(0, 10);
		composite_1.setLayoutData(fd_composite_1);
		
		Button btnSalir = new Button(composite_1, SWT.NONE);
		btnSalir.addSelectionListener(new SelectionAdapter() {
			public void widgetSelected(SelectionEvent e) {
				shlIvaListado.close();
			}
		});
		btnSalir.setAlignment(SWT.RIGHT);
		btnSalir.setFont(SWTResourceManager.getFont("Calibri", 11, SWT.BOLD));
		btnSalir.setImage(SWTResourceManager.getImage(IvaGUI.class, "/imagenes/delete-icon.png"));
		FormData fd_btnSalir = new FormData();
		fd_btnSalir.top = new FormAttachment(0, 7);
		fd_btnSalir.right = new FormAttachment(100);
		fd_btnSalir.bottom = new FormAttachment(100, -8);
		fd_btnSalir.left = new FormAttachment(100, -107);
		btnSalir.setLayoutData(fd_btnSalir);
		btnSalir.setText("Salir");
		
		Label lblProveedor = new Label(composite, SWT.NONE);
		FormData fd_lblProveedor = new FormData();
		fd_lblProveedor.top = new FormAttachment(0, 10);
		fd_lblProveedor.left = new FormAttachment(0, 5);
		lblProveedor.setLayoutData(fd_lblProveedor);
		lblProveedor.setText("Proveedor:");
		lblProveedor.setFont(SWTResourceManager.getFont("Calibri", 11, SWT.BOLD));
		
		txtIngreseElNombre = new Text(composite, SWT.BORDER);
		FormData fd_txtIngreseElNombre = new FormData();
		fd_txtIngreseElNombre.bottom = new FormAttachment(0, 33);
		fd_txtIngreseElNombre.right = new FormAttachment(0, 447);
		fd_txtIngreseElNombre.top = new FormAttachment(0, 5);
		fd_txtIngreseElNombre.left = new FormAttachment(0, 79);
		txtIngreseElNombre.setLayoutData(fd_txtIngreseElNombre);
		txtIngreseElNombre.addFocusListener(new FocusAdapter() {
			public void focusGained(FocusEvent e) {
				txtIngreseElNombre.setText("");
			}
		});
		txtIngreseElNombre.setForeground(SWTResourceManager.getColor(0, 0, 0));
		txtIngreseElNombre.setText("Ingrese el nombre completo o parte.");
		txtIngreseElNombre.setFont(SWTResourceManager.getFont("Calibri", 11, SWT.BOLD));
		
		Label lblEmpresa = new Label(composite, SWT.NONE);
		FormData fd_lblEmpresa = new FormData();
		fd_lblEmpresa.bottom = new FormAttachment(0, 60);
		fd_lblEmpresa.top = new FormAttachment(0, 41);
		fd_lblEmpresa.left = new FormAttachment(0, 17);
		lblEmpresa.setLayoutData(fd_lblEmpresa);
		lblEmpresa.setFont(SWTResourceManager.getFont("Calibri", 11, SWT.BOLD));
		lblEmpresa.setText("Empresa:");
		
		final Combo comboEmpresa = new Combo(composite, SWT.READ_ONLY);
		FormData fd_comboEmpresa = new FormData();
		fd_comboEmpresa.right = new FormAttachment(0, 447);
		fd_comboEmpresa.top = new FormAttachment(0, 38);
		fd_comboEmpresa.left = new FormAttachment(0, 79);
		comboEmpresa.setLayoutData(fd_comboEmpresa);
		comboEmpresa.setVisibleItemCount(12);
		comboEmpresa.setItems(new String[] {"SELECCIONAR EMPRESA.......", "AZTIRIA ELSA CLARA", "CLAVIER", 
				"ESTANCIA EL ALBARDON S.A.", "ESTANCIA PAMPA GRANDE S.A.", "ISABEL DUGGAN DE DI CAMPELLO", 
				"KENVIS S.A.", "LOS HUAICOS M.L. S.A.", "TRES ISLAS S.A."});
		
		comboEmpresa.setData("AZTIRIA ELSA CLARA","aztiriaiva"); 
		comboEmpresa.setData("CLAVIER","clavieriva"); 
		comboEmpresa.setData("ESTANCIA EL ALBARDON S.A.","albardoniva"); 
		comboEmpresa.setData("ESTANCIA PAMPA GRANDE S.A.","pampaiva"); 
		comboEmpresa.setData("ISABEL DUGGAN DE DI CAMPELLO","dugganiva"); 
		comboEmpresa.setData("KENVIS S.A.","kenvisiva"); 
		comboEmpresa.setData("LOS HUAICOS M.L. S.A.","huaicosiva"); 
		comboEmpresa.setData("TRES ISLAS S.A.","tisaiva"); 
		
		comboEmpresa.select(0);

		comboEmpresa.setFont(SWTResourceManager.getFont("Calibri", 11, SWT.BOLD));
		
		Label lblPeriodo = new Label(composite, SWT.NONE);
		FormData fd_lblPeriodo = new FormData();
		fd_lblPeriodo.top = new FormAttachment(0, 76);
		fd_lblPeriodo.left = new FormAttachment(0, 34);
		lblPeriodo.setLayoutData(fd_lblPeriodo);
		lblPeriodo.setText("Fecha:");
		lblPeriodo.setFont(SWTResourceManager.getFont("Calibri", 11, SWT.BOLD));
		
		final Text fecha = new Text(composite, SWT.BORDER);
		FormData fd_fecha = new FormData();
		fd_fecha.bottom = new FormAttachment(0, 99);
		fd_fecha.right = new FormAttachment(0, 224);
		fd_fecha.top = new FormAttachment(0, 71);
		fd_fecha.left = new FormAttachment(0, 79);
		fecha.setLayoutData(fd_fecha);
		fecha.setToolTipText("Formato: 01/01/2000");
		fecha.setForeground(SWTResourceManager.getColor(0, 0, 0));
		
		

		fecha.addVerifyListener(new VerifyListener() {
			public void verifyText(VerifyEvent event) {
		        event.doit = false;
		        // Allow backspace and delete
		        if (event.character == '\u0008' || event.character == '\u007F' || event.character == '\u002F')	event.doit = true;
		        if (Character.isDigit(event.character)) event.doit = true; 
		        }
		});
		fecha.setFont(SWTResourceManager.getFont("Calibri", 11, SWT.BOLD));
		
		fecha.setTextLimit(10);
		
		Label label_1 = new Label(composite, SWT.NONE);
		FormData fd_label_1 = new FormData();
		fd_label_1.bottom = new FormAttachment(lblPeriodo, 18);
		fd_label_1.top = new FormAttachment(lblPeriodo, 0, SWT.TOP);
		label_1.setLayoutData(fd_label_1);
		label_1.setText("Fecha:");
		label_1.setFont(SWTResourceManager.getFont("Calibri", 11, SWT.BOLD));
		Label label = new Label(composite, SWT.NONE);
		FormData fd_label = new FormData();
		fd_label.top = new FormAttachment(0, 78);
		fd_label.left = new FormAttachment(0, 515);
		label.setLayoutData(fd_label);
		
		text = new Text(composite, SWT.BORDER);
		fd_label_1.left = new FormAttachment(text, -46, SWT.LEFT);
		fd_label_1.right = new FormAttachment(text, -6);
		FormData fd_text = new FormData();
		fd_text.right = new FormAttachment(label, -68);
		fd_text.left = new FormAttachment(0, 302);
		fd_text.bottom = new FormAttachment(lblPeriodo, 22);
		fd_text.top = new FormAttachment(comboEmpresa, 6);
		text.setLayoutData(fd_text);
		text.setToolTipText("Formato: 01/01/2000");
		text.setTextLimit(10);
		text.setForeground(SWTResourceManager.getColor(0, 0, 0));
		text.setFont(SWTResourceManager.getFont("Calibri", 11, SWT.BOLD));
		
		final Button btnCompra = new Button(composite, SWT.RADIO);
		FormData fd_btnCompra = new FormData();
		fd_btnCompra.bottom = new FormAttachment(100, -7);
		fd_btnCompra.top = new FormAttachment(0, 76);
		btnCompra.setLayoutData(fd_btnCompra);
		btnCompra.setFont(SWTResourceManager.getFont("Calibri", 11, SWT.BOLD));
		btnCompra.setText("Compra");
		
		final Button btnVenta = new Button(composite, SWT.RADIO);
		fd_btnCompra.right = new FormAttachment(btnVenta, -6);
		FormData fd_btnVenta = new FormData();
		fd_btnVenta.top = new FormAttachment(lblPeriodo, 0, SWT.TOP);
		btnVenta.setLayoutData(fd_btnVenta);
		btnVenta.setFont(SWTResourceManager.getFont("Calibri", 11, SWT.BOLD));
		btnVenta.setText("Venta");
		composite.setTabList(new Control[]{txtIngreseElNombre, comboEmpresa, fecha, btnCompra, btnVenta});
		
		Button buscarButton = new Button(composite, SWT.NONE);
		fd_btnVenta.right = new FormAttachment(buscarButton, -6);
		FormData fd_buscarButton = new FormData();
		fd_buscarButton.right = new FormAttachment(0, 714);
		fd_buscarButton.top = new FormAttachment(0, 69);
		fd_buscarButton.bottom = new FormAttachment(100);
		fd_buscarButton.left = new FormAttachment(0, 629);
		buscarButton.setLayoutData(fd_buscarButton);
		buscarButton.addSelectionListener(new SelectionAdapter() {
			public void widgetSelected(SelectionEvent e) {
				
				table.removeAll();
				
				if (btnCompra.getSelection()==true){
					tipo = "compras";
				}
				if (btnVenta.getSelection()==true){
					tipo = "ventas";
				}
				buscarDatos(comboEmpresa.getData(comboEmpresa.getItem(comboEmpresa.getSelectionIndex())).toString(), tipo, txtIngreseElNombre.getText(), new Utility().fechaParse(fecha.getText()));
				table.setFocus();
			}
		});
		buscarButton.setText("Buscar");
		
		shlIvaListado.setDefaultButton(buscarButton);
		
		Menu menu = new Menu(shlIvaListado, SWT.BAR);
		shlIvaListado.setMenuBar(menu);
		
		MenuItem mntmArchivo = new MenuItem(menu, SWT.CASCADE);
		mntmArchivo.setText("Archivo");
		
		Menu menu_1 = new Menu(mntmArchivo);
		mntmArchivo.setMenu(menu_1);
		
		MenuItem mntmSalir = new MenuItem(menu_1, SWT.NONE);
		mntmSalir.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				shlIvaListado.close();
			}
		});
		mntmSalir.setText("Salir");
		
		MenuItem mntmProveedores = new MenuItem(menu, SWT.CASCADE);
		mntmProveedores.setText("Proveedores");
		
		Menu menu_2 = new Menu(mntmProveedores);
		mntmProveedores.setMenu(menu_2);
		
		MenuItem mntmBuscarProveedor = new MenuItem(menu_2, SWT.NONE);
		mntmBuscarProveedor.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				new BuscarProveedor().open();
			}
		});
		mntmBuscarProveedor.setText("Buscar Proveedor");
		
		MenuItem mntmFactura = new MenuItem(menu, SWT.CASCADE);
		mntmFactura.setText("Factura");
		
		Menu menu_3 = new Menu(mntmFactura);
		mntmFactura.setMenu(menu_3);
		
		MenuItem mntmBuscarFactura = new MenuItem(menu_3, SWT.NONE);
		mntmBuscarFactura.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				table.removeAll();
				BuscarFactura fc = new BuscarFactura();
				fc.open();
				System.out.println(fc.getEmpresa());
				System.out.println(fc.getTipobase());
				System.out.println(fc.getNombreproveedor());
				System.out.println(fc.getSucursal());
				System.out.println(fc.getFactura());
				
				
				
				
				
				buscarFactura(fc.getEmpresa(), fc.getTipobase(), fc.getNombreproveedor(), fc.getSucursal(), fc.getFactura());
			}
		});
		mntmBuscarFactura.setText("Buscar Factura");

	}
}
